﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.DB;
using PMPUtils.Goals;
using PMPUtils.Resources;
using PMPUtils.Report;

namespace PMPUtils.JIRA
{
    public class JIRAOperator
    {
        /// <summary>
        /// Read the summary of the JIRA.
        /// </summary>
        /// <param name="jiraPkey"></param>
        /// <returns></returns>
        public static String ReadSummary(String jiraPkey)
        {
            String summary = String.Empty;

            DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
            String strRead = "select distinct summary from jiraissue where pkey = '" + jiraPkey + "'";
            DataTable dtSummary = dbOperator.Read(strRead);

            foreach (DataRow row in dtSummary.Rows)
            {
                if (row["summary"] != DBNull.Value)
                {
                    summary = row["summary"].ToString();
                }
            }

            return summary;
        }

        /// <summary>
        /// Before sync, if not backed up today's data yet, then backup first.
        /// Update the followings of the project:
        /// 1. Goals' original estimation, remaining estimation and variance
        /// 2. Goal details' original estimation, remaining estimation and variance
        /// </summary>
        public static void SyncCurrentSprint(String sprint)
        {
            // backup data
            if (!GoalsOperator.IsBackedUpAlready(sprint))
            {
                GoalsOperator.Backup();
            }

            GoalsOperator.AddLeftOverTasks();

            // Query all current sprint tasks
            String resourceList = ResourceOperator.ReadResouceList("'NF1', 'NF2', 'OPTM'");
            DataTable dtCurrentGoals = GoalsOperator.ReadSprintGoals(sprint);

            foreach (DataRow row in dtCurrentGoals.Rows)
            {
                String goalID = row["id"].ToString();
                String defaultOwner = row["owner"].ToString();
                String defaultOwnerID = ResourceOperator.ReadResourceID(defaultOwner);

                if (row["task_id"] != DBNull.Value &&
                    !row["task_id"].ToString().Equals(String.Empty) &&
                    !row["task_id"].ToString().Equals(""))
                {
                    String taskID = row["task_id"].ToString();

                    String strQuerySubTasks = "select subtask.pkey as task_id, subtask.ASSIGNEE, subtask.DUEDATE " +
                        " from jiraissue story " +
                        " join issuelink on (issuelink.linktype = '10000' and issuelink.source = story.id) " +
                        " join jiraissue subtask on (issuelink.linktype = '10000' and issuelink.destination = subtask.id) " +
                        " where story.pkey in ('" + taskID + "') " +
                        "   and subtask.ASSIGNEE in (" + resourceList + ")";
                    DBOperator subTaskOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
                    DataTable dtSubTasks = subTaskOperator.Read(strQuerySubTasks);

                    foreach (DataRow subTaskRow in dtSubTasks.Rows)
                    {
                        if (subTaskRow["task_id"] != DBNull.Value)
                        {
                            String subTaskID = subTaskRow["task_id"].ToString();

                            // Add the goal if it doesn't exist
                            if (!GoalDetailsOperator.IsGoalDetailExists(goalID, subTaskID))
                            {
                                String owner = subTaskRow["assignee"].ToString();
                                String userID = ResourceOperator.ReadResourceID(owner);
                                if (userID.Equals(String.Empty)) { userID = defaultOwnerID; }

                                String dueDate = String.Empty;
                                if (subTaskRow["duedate"] != DBNull.Value && !subTaskRow["duedate"].ToString().Equals(String.Empty))
                                {
                                    dueDate = Convert.ToDateTime(subTaskRow["duedate"]).ToString("yyyy-MM-dd");
                                }
                                else if (row["due_date"] != DBNull.Value && !row["due_date"].ToString().Equals(String.Empty))
                                {
                                    dueDate = Convert.ToDateTime(row["due_date"]).ToString("yyyy-MM-dd");
                                }

                                GoalDetailsOperator.AddNewGoalDetail(goalID, subTaskID, userID, dueDate);
                            }
                        }
                    }
                }

                // If the goal links with a specific task, then get estimation from the database directly.
                // Otherwise, go deeper to its details to update their estimations.
                if (row["task_id"] != DBNull.Value)
                {
                    if (!row["task_id"].ToString().Trim().Equals(String.Empty))
                    {
                        String task = row["task_id"].ToString().Trim();

                        String strQuery = "select jiraissue.pkey Task, jiraissue.SUMMARY task_desc, issuestatus.pname status, cast(jiraissue.TIMEORIGINALESTIMATE/3600 as decimal(18,2)) 'Orig',  case when issuestatus.pname in ('Complete', 'Closed') then 0.00 " +
                            "       else cast(isnull(jiraissue.TIMEESTIMATE, jiraissue.TIMEORIGINALESTIMATE)/3600 as decimal(18,2)) + isnull(sum(cast(subtask.TIMEESTIMATE/3600 as decimal(18,2))), isnull(sum(cast(subtask.TIMEORIGINALESTIMATE/3600 as decimal(18,2))), 0.00)) end 'RemEst', " +
                            "       isnull(sum(cast(jiraissue.TIMESPENT/3600 as decimal(18,2))), 0.0) + isnull(sum(cast(subtask.TIMESPENT/3600 as decimal(18,2))), 0.00) 'Actual', " +
                            "        (isnull(sum(cast(jiraissue.TIMESPENT/3600 as decimal(18,2))), 0.0) + isnull(sum(cast(subtask.TIMESPENT/3600 as decimal(18,2))), 0.00) - cast(jiraissue.TIMEORIGINALESTIMATE/3600 as decimal(18,2)))/cast(jiraissue.TIMEORIGINALESTIMATE/3600 as decimal(18,2)) 'Variance' " +
                            " from jiraissue " +
                            " join issuestatus on (issuestatus.id = jiraissue.issuestatus) " +
                            " left outer join issuelink on (issuelink.linktype = '10000' and issuelink.source = jiraissue.id) " +
                            " left outer join jiraissue subtask on (issuelink.linktype = '10000' and issuelink.destination = subtask.id and (subtask.assignee is null or subtask.assignee in (" + resourceList + "))) " +
                            " where jiraissue.pkey in ('" + task + "') " +
                            " group by jiraissue.pkey,jiraissue.SUMMARY, issuestatus.pname, issuestatus.pname, cast(isnull(jiraissue.TIMEESTIMATE, jiraissue.TIMEORIGINALESTIMATE)/3600 as decimal(18,2)), cast(jiraissue.TIMEORIGINALESTIMATE/3600 as decimal(18,2))";
                        DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
                        DataTable dtEstimations = dbOperator.Read(strQuery);

                        foreach (DataRow resultRow in dtEstimations.Rows)
                        {
                            if (resultRow["Orig"] != DBNull.Value)
                            {
                                double originalEstimation = resultRow["Orig"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["Orig"]);
                                double remainingEstimation = resultRow["RemEst"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["RemEst"]);
                                double actualTimeSpent = resultRow["Actual"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["Actual"]);
                                double variance = resultRow["Variance"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["Variance"]);
                                String summary = resultRow["task_desc"].ToString();
                                String status = resultRow["status"].ToString();
                                if (status.Equals("Need More Information")) { status = "NMI"; }

                                String strUpdate = "update goals set orig_est = " + originalEstimation +
                                    ", rem_est = " + remainingEstimation +
                                    ", actual = " + actualTimeSpent +
                                    ", variance = " + variance +
                                    ", task_desc = '" + summary.Replace("\'", "") + "'" +
                                    ", status = '" + status + "'" +
                                    " where id = " + goalID;
                                dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                                dbOperator.Update(strUpdate);
                            }
                        }
                    }
                }

                // Update all details information
                DataTable dtDetails = GoalDetailsOperator.ReadGoalDetails(goalID);

                foreach (DataRow detailRow in dtDetails.Rows)
                {
                    String goalDetailsID = detailRow["ID"].ToString().Trim();

                    if (detailRow["task_id"] != DBNull.Value)
                    {
                        String taskID = detailRow["task_id"].ToString();
                        String strQuery = " select jiraissue.SUMMARY task_desc, jiraissue.assignee, issuestatus.pname status, cast(jiraissue.TIMEORIGINALESTIMATE/3600 as decimal(18,2)) 'Orig', " +
                            " isnull(cast(jiraissue.TIMESPENT/3600 as decimal(18,2)), 0.0) 'Actual', " +
                            " isnull(cast(jiraissue.TIMEESTIMATE/3600 as decimal(18,2)), 0.0) 'RemEst', " +
                            " case when jiraissue.TIMEORIGINALESTIMATE is null or jiraissue.TIMEORIGINALESTIMATE = 0.00 then -1.00 else cast((isnull(cast(jiraissue.TIMESPENT/3600 as decimal(18,2)), 0.0) - cast(jiraissue.TIMEORIGINALESTIMATE/3600 as decimal(18,2))) / cast(jiraissue.TIMEORIGINALESTIMATE/3600 as decimal(18,2)) as decimal(18, 4)) end 'Variance'  " +
                            "   from jiraissue " +
                            "   join issuestatus on (jiraissue.issuestatus = issuestatus.ID) " +
                            "  where  jiraissue.pkey in ('" + taskID + "')";
                        DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
                        DataTable dtEstimations = dbOperator.Read(strQuery);

                        foreach (DataRow resultRow in dtEstimations.Rows)
                        {
                            if (resultRow["Orig"] != DBNull.Value)
                            {
                                double originalEstimation = resultRow["Orig"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["Orig"]);
                                double remainingEstimation = resultRow["RemEst"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["RemEst"]);
                                double actualTimeSpent = resultRow["Actual"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["Actual"]);
                                double variance = resultRow["Variance"] == DBNull.Value ? 0.00 : Convert.ToDouble(resultRow["Variance"]);
                                String summary = resultRow["task_desc"].ToString();
                                String status = resultRow["status"].ToString();
                                if (status.Equals("Need More Information")) { status = "NMI"; }
                                String owner = resultRow["assignee"].ToString();
                                if (owner.Equals(String.Empty)) { owner = defaultOwner; }
                                String ownerID = ResourceOperator.ReadResourceID(owner);

                                String strUpdate = "update goal_dtl set orig_est = " + originalEstimation +
                                    ", owner = " + ownerID +
                                    ", rem_est = " + remainingEstimation +
                                    ", actual = " + actualTimeSpent + 
                                    ", variance = " + variance +
                                    ", task_desc = '" + summary.Replace("\'", "") + "'" +
                                    ", status = '" + status + "'" +
                                    " where id = " + goalDetailsID;
                                dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                                dbOperator.Update(strUpdate);
                            }
                        }
                    }
                }
            }

            // Update goal tasks if the goal is not associated with any specific task
            DataTable dtNonSpecificTasks = GoalsOperator.ReadGoalsWithoutSpecificTask(sprint);
            foreach (DataRow row in dtNonSpecificTasks.Rows)
            {
                if (row["ID"] != DBNull.Value)
                {
                    String goalID = row["ID"].ToString().Trim();
                    DataTable dtDetails = GoalDetailsOperator.ReadGoalDetails(goalID);
                    double remainingEstimation = 0.00;
                    double actual = 0.00;

                    foreach (DataRow detailRow in dtDetails.Rows)
                    {
                        if (row["ID"] != DBNull.Value)
                        {
                            remainingEstimation += detailRow["rem_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(detailRow["rem_est"]);

                            double originalEstimation = detailRow["orig_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(detailRow["orig_est"]);
                            double variance = detailRow["Variance"] == DBNull.Value ? 0.00 : Convert.ToDouble(detailRow["Variance"]);
                            actual += originalEstimation * (1 + variance);
                        }
                    }

                    // Update goals remaining estimations
                    String strUpdate = "update goals set rem_est = " + remainingEstimation +
                        " where id = " + goalID;
                    DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    dbOperator.Update(strUpdate);

                    // Update the goal's variances
                    String strUpdateVariance = "update goals set variance = (" + actual + " - orig_est) / orig_est where id = " + goalID;
                    dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    dbOperator.Update(strUpdateVariance);
                }
            }

            UpdateDummyGoalsVarainces(sprint);

            EVM(sprint);

            BurndownOperator.UpdateBurndown(sprint);
        }

        /// <summary>
        /// Update the EAC and ETC
        /// </summary>
        public static void EVM(String sprint)
        {
            DataTable dtGoals = GoalsOperator.ReadInProgressGoals(sprint);
            foreach (DataRow goalRow in dtGoals.Rows)
            {
                if (goalRow["id"] != DBNull.Value)
                {
                    String goal = goalRow["id"].ToString();
                    double goalRemainingEstimation =
                        goalRow["rem_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(goalRow["rem_est"]);
                    DataTable dtDetails = GoalDetailsOperator.ReadCompletedGoalDetails(goal);
                    double scheduleVariance = 0.00;
                    double actualTimeSpent = 0.00;
                    double originalEstimation = 0.00;

                    if (dtDetails.Rows.Count >= 1)
                    {
                        foreach (DataRow detailRow in dtDetails.Rows)
                        {
                            if (detailRow["id"] != DBNull.Value)
                            {
                                originalEstimation += detailRow["orig_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(detailRow["orig_est"]);
                                actualTimeSpent += detailRow["actual"] == DBNull.Value ? 0.00 : Convert.ToDouble(detailRow["actual"]);
                            }
                        }

                        scheduleVariance = originalEstimation / actualTimeSpent;
                        double goalOrigEst = goalRow["orig_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(goalRow["orig_est"]);
                        double ETC = goalRemainingEstimation / scheduleVariance;
                        double EAC = goalOrigEst / scheduleVariance;
                        if (ETC <= 0) { ETC = EAC; }
                        

                        String strUpdate = "update goals set " +
                            "  EAC = " + EAC +
                            ", ETC = " + ETC +
                            " where id = " + goal;

                        DBOperator goalOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                        goalOperator.Update(strUpdate);

                        DataTable dtInProgressDetails = GoalDetailsOperator.ReadInProgressGoalDetails(goal);
                        foreach (DataRow inProgressRow in dtInProgressDetails.Rows)
                        {
                            if (inProgressRow["id"] != DBNull.Value)
                            {
                                double inProgressOrigEst = inProgressRow["orig_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(inProgressRow["orig_est"]);
                                double inProgressActual = inProgressRow["actual"] == DBNull.Value ? 0.00 : Convert.ToDouble(inProgressRow["actual"]);
                                double inProgressRemEst = inProgressRow["rem_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(inProgressRow["rem_est"]);
                                double detailEAC = 0.00;
                                double detailETC = 0.00;

                                detailEAC = inProgressOrigEst / scheduleVariance;
                                detailETC = inProgressRemEst / scheduleVariance;
                                if (detailETC <= 0) { detailETC = detailEAC; }

                                String strUpdateDetails = "update goal_dtl set " +
                                    "  EAC = " + detailEAC +
                                    ", ETC = " + detailETC +
                                    " where id = " + inProgressRow["id"].ToString();
                                goalOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                                goalOperator.Update(strUpdateDetails);
                            }
                        }
                    }
                }
            }
        }

        private static void UpdateDummyGoalsVarainces(String sprint)
        {

            DataTable dtNonSpecificTasks = GoalsOperator.ReadGoalsWithoutSpecificTask(sprint);
            foreach (DataRow row in dtNonSpecificTasks.Rows)
            {
                if (row["id"] != DBNull.Value)
                {
                    String goalID = row["ID"].ToString();

                    // Update actual and remaining of the dummy goals
                    String strUpdateActualAndRemaining = "update goals " +
                        " set actual = (select sum(goal_dtl.actual) from goals join goal_dtl on (goal_dtl.parent_id = goals.id) where goals.sprint = " + sprint + " and goals.rev is null and (goals.task_id is null or goals.task_id = '') and goals.id =  " + goalID + "  and goal_dtl.rev is null group by goals.goal, goals.orig_est), " +
                        " rem_est = (select goals.orig_est - sum(goal_dtl.actual) from goals join goal_dtl on (goal_dtl.parent_id = goals.id) where goals.sprint = " + sprint + " and goals.rev is null and (goals.task_id is null or goals.task_id = '') and goals.id =  " + goalID + "  and goal_dtl.rev is null group by goals.goal, goals.orig_est) " +
                        " where goals.id = " + goalID + "";
                    DBOperator localDBOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    localDBOperator.Update(strUpdateActualAndRemaining);

                    // Update the variances for dummy goals
                    String strUpdateVariance = "update goals set variance = (actual - orig_est) / CAST(orig_est as decimal(18, 2)) where goals.sprint = " + sprint + " and goals.rev is null and (goals.task_id is null or goals.task_id = '')";
                    localDBOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    localDBOperator.Update(strUpdateVariance);
                }
            }
        }
    }
}